import time
import psutil
from datetime import datetime, timedelta
from django.utils import timezone
from .models import DatabaseConnection, DatabaseMonitoring, DatabaseAlert
from .managers import DatabaseConnectionManager


class DatabaseMonitoringManager:
    """数据库监控管理器"""
    
    def __init__(self):
        # 监控阈值配置
        self.thresholds = {
            'cpu_usage': 80.0,      # CPU使用率阈值
            'memory_usage': 85.0,   # 内存使用率阈值
            'disk_usage': 90.0,     # 磁盘使用率阈值
            'connection_count': 100, # 连接数阈值
            'response_time': 1000.0, # 响应时间阈值(毫秒)
            'slow_queries': 10       # 慢查询数阈值
        }
    
    def collect_metrics(self, db_conn):
        """收集数据库指标"""
        try:
            # 获取系统指标
            cpu_usage = psutil.cpu_percent(interval=1)
            memory = psutil.virtual_memory()
            disk = psutil.disk_usage('/')
            
            # 获取数据库特定指标
            db_metrics = self._get_database_metrics(db_conn)
            
            # 创建监控记录
            monitoring_record = DatabaseMonitoring.objects.create(
                connection=db_conn,
                cpu_usage=cpu_usage,
                memory_usage=memory.percent,
                disk_usage=disk.percent,
                connection_count=db_metrics.get('connection_count', 0),
                active_queries=db_metrics.get('active_queries', 0),
                slow_queries=db_metrics.get('slow_queries', 0),
                table_count=db_metrics.get('table_count', 0),
                database_size=db_metrics.get('database_size', 0),
                response_time=db_metrics.get('response_time', 0)
            )
            
            # 检查告警条件
            self._check_alerts(db_conn, monitoring_record)
            
            return {
                'success': True,
                'monitoring_id': monitoring_record.id,
                'metrics': {
                    'cpu_usage': cpu_usage,
                    'memory_usage': memory.percent,
                    'disk_usage': disk.percent,
                    'connection_count': db_metrics.get('connection_count', 0),
                    'active_queries': db_metrics.get('active_queries', 0),
                    'slow_queries': db_metrics.get('slow_queries', 0),
                    'table_count': db_metrics.get('table_count', 0),
                    'database_size': db_metrics.get('database_size', 0),
                    'response_time': db_metrics.get('response_time', 0)
                }
            }
            
        except Exception as e:
            return {
                'success': False,
                'error': str(e),
                'message': f'收集监控指标失败: {str(e)}'
            }
    
    def _get_database_metrics(self, db_conn):
        """获取数据库特定指标"""
        start_time = time.time()
        
        try:
            connection = DatabaseConnectionManager.create_connection({
                'engine': db_conn.engine,
                'host': db_conn.host,
                'port': db_conn.port,
                'username': db_conn.username,
                'password': db_conn.password,
                'database': db_conn.database
            })
            
            cursor = connection.cursor()
            metrics = {}
            
            if db_conn.engine == 'mysql':
                metrics = self._get_mysql_metrics(cursor, db_conn)
            elif db_conn.engine == 'postgresql':
                metrics = self._get_postgresql_metrics(cursor, db_conn)
            elif db_conn.engine == 'sqlite3':
                metrics = self._get_sqlite_metrics(cursor, db_conn)
            
            # 计算响应时间
            response_time = (time.time() - start_time) * 1000
            metrics['response_time'] = response_time
            
            cursor.close()
            connection.close()
            
            return metrics
            
        except Exception as e:
            # 如果无法连接，返回默认值
            response_time = (time.time() - start_time) * 1000
            return {
                'connection_count': 0,
                'active_queries': 0,
                'slow_queries': 0,
                'table_count': 0,
                'database_size': 0,
                'response_time': response_time
            }
    
    def _get_mysql_metrics(self, cursor, db_conn):
        """获取MySQL指标"""
        metrics = {}
        
        try:
            # 获取连接数
            cursor.execute("SHOW STATUS LIKE 'Threads_connected'")
            result = cursor.fetchone()
            metrics['connection_count'] = int(result[1]) if result else 0
            
            # 获取活跃查询数
            cursor.execute("SHOW PROCESSLIST")
            active_queries = len(cursor.fetchall())
            metrics['active_queries'] = active_queries
            
            # 获取慢查询数（从状态变量）
            cursor.execute("SHOW STATUS LIKE 'Slow_queries'")
            result = cursor.fetchone()
            metrics['slow_queries'] = int(result[1]) if result else 0
            
            # 获取表数量
            cursor.execute("SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = %s", (db_conn.database,))
            result = cursor.fetchone()
            metrics['table_count'] = result[0] if result else 0
            
            # 获取数据库大小
            cursor.execute("""
                SELECT SUM(data_length + index_length) 
                FROM information_schema.tables 
                WHERE table_schema = %s
            """, (db_conn.database,))
            result = cursor.fetchone()
            metrics['database_size'] = result[0] if result and result[0] else 0
            
        except Exception as e:
            print(f"获取MySQL指标失败: {e}")
        
        return metrics
    
    def _get_postgresql_metrics(self, cursor, db_conn):
        """获取PostgreSQL指标"""
        metrics = {}
        
        try:
            # 获取连接数
            cursor.execute("SELECT count(*) FROM pg_stat_activity")
            result = cursor.fetchone()
            metrics['connection_count'] = result[0] if result else 0
            
            # 获取活跃查询数
            cursor.execute("SELECT count(*) FROM pg_stat_activity WHERE state = 'active'")
            result = cursor.fetchone()
            metrics['active_queries'] = result[0] if result else 0
            
            # 获取慢查询数（这里简化处理）
            metrics['slow_queries'] = 0
            
            # 获取表数量
            cursor.execute("SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'public'")
            result = cursor.fetchone()
            metrics['table_count'] = result[0] if result else 0
            
            # 获取数据库大小
            cursor.execute("SELECT pg_database_size(%s)", (db_conn.database,))
            result = cursor.fetchone()
            metrics['database_size'] = result[0] if result else 0
            
        except Exception as e:
            print(f"获取PostgreSQL指标失败: {e}")
        
        return metrics
    
    def _get_sqlite_metrics(self, cursor, db_conn):
        """获取SQLite指标"""
        metrics = {}
        
        try:
            # SQLite是文件数据库，连接数固定为1
            metrics['connection_count'] = 1
            metrics['active_queries'] = 1
            metrics['slow_queries'] = 0
            
            # 获取表数量
            cursor.execute("SELECT COUNT(*) FROM sqlite_master WHERE type='table'")
            result = cursor.fetchone()
            metrics['table_count'] = result[0] if result else 0
            
            # 获取数据库大小（文件大小）
            import os
            if os.path.exists(db_conn.database):
                metrics['database_size'] = os.path.getsize(db_conn.database)
            else:
                metrics['database_size'] = 0
                
        except Exception as e:
            print(f"获取SQLite指标失败: {e}")
        
        return metrics
    
    def _check_alerts(self, db_conn, monitoring_record):
        """检查告警条件"""
        alerts = []
        
        # 检查CPU使用率
        if monitoring_record.cpu_usage > self.thresholds['cpu_usage']:
            alerts.append({
                'alert_type': 'high_cpu',
                'severity': 'high' if monitoring_record.cpu_usage > 90 else 'medium',
                'message': f'CPU使用率过高: {monitoring_record.cpu_usage:.1f}%',
                'threshold_value': self.thresholds['cpu_usage'],
                'current_value': monitoring_record.cpu_usage
            })
        
        # 检查内存使用率
        if monitoring_record.memory_usage > self.thresholds['memory_usage']:
            alerts.append({
                'alert_type': 'high_memory',
                'severity': 'high' if monitoring_record.memory_usage > 95 else 'medium',
                'message': f'内存使用率过高: {monitoring_record.memory_usage:.1f}%',
                'threshold_value': self.thresholds['memory_usage'],
                'current_value': monitoring_record.memory_usage
            })
        
        # 检查磁盘使用率
        if monitoring_record.disk_usage > self.thresholds['disk_usage']:
            alerts.append({
                'alert_type': 'high_disk',
                'severity': 'critical' if monitoring_record.disk_usage > 95 else 'high',
                'message': f'磁盘使用率过高: {monitoring_record.disk_usage:.1f}%',
                'threshold_value': self.thresholds['disk_usage'],
                'current_value': monitoring_record.disk_usage
            })
        
        # 检查连接数
        if monitoring_record.connection_count > self.thresholds['connection_count']:
            alerts.append({
                'alert_type': 'too_many_connections',
                'severity': 'high',
                'message': f'数据库连接数过多: {monitoring_record.connection_count}',
                'threshold_value': self.thresholds['connection_count'],
                'current_value': monitoring_record.connection_count
            })
        
        # 检查响应时间
        if monitoring_record.response_time > self.thresholds['response_time']:
            alerts.append({
                'alert_type': 'slow_query',
                'severity': 'medium',
                'message': f'数据库响应时间过长: {monitoring_record.response_time:.1f}ms',
                'threshold_value': self.thresholds['response_time'],
                'current_value': monitoring_record.response_time
            })
        
        # 创建告警记录
        for alert_data in alerts:
            # 检查是否已存在相同类型的未解决告警
            existing_alert = DatabaseAlert.objects.filter(
                connection=db_conn,
                alert_type=alert_data['alert_type'],
                is_resolved=False
            ).first()
            
            if not existing_alert:
                DatabaseAlert.objects.create(
                    connection=db_conn,
                    **alert_data
                )
    
    def get_monitoring_history(self, db_conn, hours=24):
        """获取监控历史数据"""
        start_time = timezone.now() - timedelta(hours=hours)
        
        return DatabaseMonitoring.objects.filter(
            connection=db_conn,
            created_at__gte=start_time
        ).order_by('created_at')
    
    def get_performance_summary(self, db_conn, hours=24):
        """获取性能摘要"""
        history = self.get_monitoring_history(db_conn, hours)
        
        if not history.exists():
            return None
        
        # 计算平均值和最大值
        from django.db.models import Avg, Max
        
        summary = history.aggregate(
            avg_cpu=Avg('cpu_usage'),
            max_cpu=Max('cpu_usage'),
            avg_memory=Avg('memory_usage'),
            max_memory=Max('memory_usage'),
            avg_disk=Avg('disk_usage'),
            max_disk=Max('disk_usage'),
            avg_connections=Avg('connection_count'),
            max_connections=Max('connection_count'),
            avg_response_time=Avg('response_time'),
            max_response_time=Max('response_time')
        )
        
        return summary
